package merlin.dao;

import java.io.*;
import java.util.*;
import java.sql.*;

import merlin.beans.*;
import merlin.utils.*;

public class CompanyWebsiteDetailsDAO {

	Connection con;

	public CompanyWebsiteDetailsDAO(Connection con){
		this.con = con;
	}

	public CompanyWebsiteDetailsBean getCompanyWebsiteDetails(String registeredNumber)throws SQLException{
		String sql = "SELECT MCWD_REGISTERED_NUMBER,MCWD_WEBSITE,MCWD_EMAIL,MCWS_UNSUBSCRIBE ,MCWD_PERSONAL_EMAIL,MCWD_PE_PREFIX,MCWD_PE_FIRST_NAME,MCWD_PE_INITIALS,MCWD_PE_SURNAME,MCWD_PE_POSITION,MCWD_PE_UNSUBSCRIBE FROM MSAL_COMPANY_WEBSITE_DETAILS WHERE MCWD_REGISTERED_NUMBER = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,registeredNumber);
		CompanyWebsiteDetailsBean bean = null;

		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			bean = new CompanyWebsiteDetailsBean();
			bean.setRegisteredNumber(rs.getString("MCWD_REGISTERED_NUMBER"));
			bean.setWebsite(rs.getString("MCWD_WEBSITE"));
			bean.setEmail(rs.getString("MCWD_EMAIL"));
			bean.setUnsubscribe(rs.getBoolean("MCWS_UNSUBSCRIBE"));
			bean.setPersonalEmail(rs.getString("MCWD_PERSONAL_EMAIL"));
			bean.setPrefix(rs.getString("MCWD_PE_PREFIX"));
			bean.setFirstName(rs.getString("MCWD_PE_FIRST_NAME"));
			bean.setInitials(rs.getString("MCWD_PE_INITIALS"));
			bean.setSurname(rs.getString("MCWD_PE_SURNAME"));
			bean.setPosition(rs.getString("MCWD_PE_POSITION"));
			bean.setPersonalUnsubscribe(rs.getBoolean("MCWD_PE_UNSUBSCRIBE"));
		}
		rs.close();
		pstmt.close();

		return bean;
	}

	public int addCompanyWebsiteDetails(CompanyWebsiteDetailsBean bean)throws SQLException{
		//String sql = "INSERT INTO MSAL_COMPANY_WEBSITE_DETAILS(MCWD_REGISTERED_NUMBER,MCWD_WEBSITE,MCWD_EMAIL,MCWS_DATE_UPDATED,MCWS_UNSUBSCRIBE) VALUES(?,?,?,CURRENT_DATE," + bean.isUnsubscribe()+ ")";
		String sql = "INSERT INTO MSAL_COMPANY_WEBSITE_DETAILS(MCWD_REGISTERED_NUMBER,MCWD_WEBSITE,MCWD_EMAIL,MCWS_DATE_UPDATED,MCWD_PERSONAL_EMAIL,MCWD_PE_PREFIX,MCWD_PE_FIRST_NAME,MCWD_PE_INITIALS,MCWD_PE_SURNAME,MCWD_PE_POSITION) VALUES(?,?,?,CURRENT_DATE,?,?,?,?,?,?)";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,bean.getRegisteredNumber());
		if(bean.getWebsite() != null){
			pstmt.setString(2,bean.getWebsite());
		}else{
			pstmt.setNull(2,Types.VARCHAR);
		}
		if(bean.getEmail() != null){
			pstmt.setString(3,bean.getEmail());
		}else{
			pstmt.setNull(3,Types.VARCHAR);
		}
		if(bean.getPersonalEmail() != null){
			pstmt.setString(4,bean.getPersonalEmail());
		}else{
			pstmt.setNull(4,Types.VARCHAR);
		}
		pstmt.setString(5,bean.getPrefix());
		pstmt.setString(6,bean.getFirstName());
		pstmt.setString(7,bean.getInitials());
		pstmt.setString(8,bean.getSurname());
		pstmt.setString(9,bean.getPosition());

    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();

    	return updateCols;
	}

	public int updateCompanyWebsiteDetails(CompanyWebsiteDetailsBean bean)throws SQLException{

    	PreparedStatement pstmt = null;

		//String sql = "UPDATE MSAL_COMPANY_WEBSITE_DETAILS SET MCWD_WEBSITE = ? ,MCWD_EMAIL = ? ,MCWS_DATE_UPDATED = CURRENT_DATE,MCWS_UNSUBSCRIBE = " + bean.isUnsubscribe() + " WHERE MCWD_REGISTERED_NUMBER = ?";
		String sql = "UPDATE MSAL_COMPANY_WEBSITE_DETAILS SET MCWD_WEBSITE = ? ,MCWD_EMAIL = ? ,MCWS_DATE_UPDATED = CURRENT_DATE ,MCWD_PERSONAL_EMAIL = ? ,MCWD_PE_PREFIX = ? ,MCWD_PE_FIRST_NAME = ? ,MCWD_PE_INITIALS = ? ,MCWD_PE_SURNAME = ? ,MCWD_PE_POSITION = ? WHERE MCWD_REGISTERED_NUMBER = ?";

		pstmt = con.prepareStatement(sql);
		if(bean.getWebsite() != null){
			pstmt.setString(1,bean.getWebsite());
		}else{
			pstmt.setNull(1,Types.VARCHAR);
		}
		if(bean.getEmail() != null){
			pstmt.setString(2,bean.getEmail());
		}else{
			pstmt.setNull(2,Types.VARCHAR);
		}
		//MCWD_PERSONAL_EMAIL = ? ,MCWD_PE_PREFIX = ? ,MCWD_PE_FIRST_NAME = ? ,
		//MCWD_PE_INITIALS = ? ,MCWD_PE_SURNAME = ? ,MCWD_PE_POSITION = ?
		if(bean.getPersonalEmail() != null){
			pstmt.setString(3,bean.getPersonalEmail());
		}else{
			pstmt.setNull(3,Types.VARCHAR);
		}
		pstmt.setString(4,bean.getPrefix());
		pstmt.setString(5,bean.getFirstName());
		pstmt.setString(6,bean.getInitials());
		pstmt.setString(7,bean.getSurname());
		pstmt.setString(8,bean.getPosition());
		pstmt.setString(9,bean.getRegisteredNumber());

    	int updateCols = pstmt.executeUpdate();
    	pstmt.close();

    	return updateCols;
	}

	/**
	 * depending on whether a record already exists or not, it update/adds the given VO.
	 */
	public int saveCompanyWebsiteDetails(CompanyWebsiteDetailsBean bean)throws SQLException{
		String sql = "SELECT 1 FROM MSAL_COMPANY_WEBSITE_DETAILS WHERE MCWD_REGISTERED_NUMBER = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,bean.getRegisteredNumber());

		ResultSet rs = pstmt.executeQuery();

		boolean exists = false;
		if(rs.next()){
			exists = true;
		}
		rs.close();
		pstmt.close();

		if(exists){
			return updateCompanyWebsiteDetails(bean);
		}else{
			return addCompanyWebsiteDetails(bean);
		}
	}

	public List getCompanyDetailsByReportId(int reportId,String orderBy)throws SQLException{
		List results = new ArrayList();

		String sql = "SELECT " +
			" MSAL_R_REGISTERED_NUMBER,MSAL_R_COMPANY_NAME AS COMPANY_NAME " +
			" FROM MSAL_REPORTS_V2 " +
			" WHERE MSAL_R_REPORT_NUMBER = ? ";


		if(orderBy != null && orderBy.length() > 0){
			sql += " ORDER BY " + orderBy;
		}

		System.out.println("\n" + sql + " using report id : " + reportId + "\n");
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,reportId);
		ResultSet rs = pstmt.executeQuery();

 		while(rs.next()){
 			CompanyWebsiteDetailsBean bean = new CompanyWebsiteDetailsBean();
 			bean.setRegisteredNumber(rs.getString("MSAL_R_REGISTERED_NUMBER"));
 			bean.setCompanyName(MerlinUtilities.cutAfterNValues(MerlinUtilities.capitalizeFully(rs.getString("COMPANY_NAME")),50));
            results.add(bean);
 		}

 		rs.close();
 		pstmt.close();

 		return results;
	}

	public List getCompanyWebsiteDetailsByReportId(int reportId,List tmpResults)throws SQLException{

		List results = new ArrayList();

		long startTime = System.currentTimeMillis();

		Map mapCompanies = new LinkedHashMap();

 		for(int i=0;i<tmpResults.size();i++){
 			CompanyWebsiteDetailsBean bean = (CompanyWebsiteDetailsBean)tmpResults.get(i);
            mapCompanies.put(bean.getRegisteredNumber(),bean);
 		}

 		long timeTaken = System.currentTimeMillis() - startTime;
		System.out.println("time taken for lookup table " + timeTaken + " ms");

		startTime = System.currentTimeMillis();


		String sql = "SELECT MCWD_REGISTERED_NUMBER, COALESCE(MCWD_WEBSITE,'') as MCWD_WEBSITE, COALESCE(MCWD_EMAIL,'') as MCWD_EMAIL,MCWS_UNSUBSCRIBE, " +
			  " COALESCE(MCWD_PERSONAL_EMAIL,'') as MCWD_PERSONAL_EMAIL,COALESCE(MCWD_PE_PREFIX,'') as MCWD_PE_PREFIX,COALESCE(MCWD_PE_FIRST_NAME,'') as MCWD_PE_FIRST_NAME,COALESCE(MCWD_PE_INITIALS,'') as MCWD_PE_INITIALS,COALESCE(MCWD_PE_SURNAME,'') as MCWD_PE_SURNAME,COALESCE(MCWD_PE_POSITION,'') as MCWD_PE_POSITION,MCWD_PE_UNSUBSCRIBE " +
			  " FROM MSAL_REPORTS,MSAL_COMPANY_WEBSITE_DETAILS " +
			  " WHERE MSAL_R_REPORT_NUMBER = ? AND MSAL_R_REGISTERED_NUMBER = MCWD_REGISTERED_NUMBER ";

		System.out.println("\n" + sql + " with report id : " + reportId + "\n");
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,reportId);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			String regId = rs.getString("MCWD_REGISTERED_NUMBER");
			Object obj =  mapCompanies.get(regId);
			if(obj != null){
				CompanyWebsiteDetailsBean bean = (CompanyWebsiteDetailsBean)obj;
	 			bean.setWebsite(rs.getString("MCWD_WEBSITE"));
 				bean.setEmail(rs.getString("MCWD_EMAIL"));
 				bean.setUnsubscribe(rs.getBoolean("MCWS_UNSUBSCRIBE"));
				bean.setPersonalEmail(rs.getString("MCWD_PERSONAL_EMAIL"));
				bean.setPrefix(rs.getString("MCWD_PE_PREFIX"));
				bean.setFirstName(rs.getString("MCWD_PE_FIRST_NAME"));
				bean.setInitials(rs.getString("MCWD_PE_INITIALS"));
				bean.setSurname(rs.getString("MCWD_PE_SURNAME"));
				bean.setPosition(rs.getString("MCWD_PE_POSITION"));
				bean.setPersonalUnsubscribe(rs.getBoolean("MCWD_PE_UNSUBSCRIBE"));
			}
		}
 		rs.close();
 		pstmt.close();

 		results.addAll(mapCompanies.values());

 		timeTaken = System.currentTimeMillis() - startTime;
		System.out.println("time taken for WebsiteDetails table " + timeTaken + " ms");

 		return results;
	}

	public int unsubscribeEmailIds(List lstEmailIds)throws SQLException{
		int updateCount = 0;

		StringBuilder sql = new StringBuilder("UPDATE MSAL_COMPANY_WEBSITE_DETAILS SET MCWS_UNSUBSCRIBE = true WHERE  LOWER(MCWD_EMAIL) IN (");
		for(int i=0;i<lstEmailIds.size()-1;i++){
			sql.append("'").append(lstEmailIds.get(i)).append("',");
		}
		sql.append("'").append(lstEmailIds.get(lstEmailIds.size() -1)).append("')");

		System.out.println(sql);

		Statement stmt = con.createStatement();

		updateCount = stmt.executeUpdate(sql.toString());

		stmt.close();

		return updateCount;
	}

	public Map getMailingListVOs(int recordLimit)throws SQLException{
		Map mapMailingListVOs = new HashMap();

		Statement stmt = con.createStatement();
		String sql = null;

		if(recordLimit < 0){
			sql = "SELECT MCWD_REGISTERED_NUMBER,MCWD_EMAIL FROM NSD.MSAL_COMPANY_WEBSITE_DETAILS WHERE  MCWS_UNSUBSCRIBE IS NULL AND MCWD_EMAIL IS NOT NULL";
		}else{
			sql = "SELECT TOP(0," + recordLimit + ") MCWD_REGISTERED_NUMBER,MCWD_EMAIL FROM NSD.MSAL_COMPANY_WEBSITE_DETAILS WHERE  MCWS_UNSUBSCRIBE IS NULL AND MCWD_EMAIL IS NOT NULL";
		}
		ResultSet rs = stmt.executeQuery(sql);

        while(rs.next()){
			MailingListVO vo = new MailingListVO(rs.getString(1),rs.getString(2));
			mapMailingListVOs.put(vo.getCompanyRegisteredNumber(),vo);
		}

		rs.close();
		stmt.close();

		return mapMailingListVOs;

	}

	public int cleanWebsiteNames()throws SQLException{
		Statement stmt = con.createStatement();

		int result = stmt.executeUpdate("update MSAL_COMPANY_WEBSITE_DETAILS set MCWD_WEBSITE = substring(trim(MCWD_WEBSITE) from 8) where position('http://' in lower(trim(MCWD_WEBSITE))) = 1");

		stmt.close();

		return result;
	}

	public int cleanEmailAddresses()throws SQLException{
		Statement stmt = con.createStatement();

		int result = stmt.executeUpdate("update MSAL_COMPANY_WEBSITE_DETAILS set MCWD_EMAIL = trim(trailing '.' from MCWD_EMAIL) where MCWD_EMAIL is not NULL");
		stmt.close();

		return result;
	}


	//******** Functions related to email list generation SQL activitiies. ***********//
	public int getNewEmailListDownloadHistoryId()throws SQLException{
		// get a new id.
    	Statement stmt = con.createStatement();
    	ResultSet rs = stmt.executeQuery("select max(coalesce(EMAIL_LIST_DOWNLOAD_HISTORY_ID,0)) + 1 from MSAL_EMAIL_LIST_DOWNLOAD_HISTORY");
    	rs.next();
		int newId = rs.getInt(1);
		rs.close();
		stmt.close();

		// a bug in the FrontBase impl returns NULL when there is no record in the table.
		if(newId == 0)newId = 1;
		return newId;
	}

	public int addEmailListGenerationEntry(String listDescription)throws SQLException{

    	int newId = getNewEmailListDownloadHistoryId();

		// insert the new record.
		PreparedStatement pstmt = con.prepareStatement("insert into MSAL_EMAIL_LIST_DOWNLOAD_HISTORY(EMAIL_LIST_DOWNLOAD_HISTORY_ID,EMAIL_LIST_DESCRIPTION,CREATION_DATE) values(?,?,current_date)");
		pstmt.setInt(1,newId);
		pstmt.setString(2,listDescription);
		pstmt.executeUpdate();

		pstmt.close();

		return newId;
	}

	// add the newly created email list into the BLOB column.
	public int addEmailListing(int recordId,byte[] byteData)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("update MSAL_EMAIL_LIST_DOWNLOAD_HISTORY set EMAIL_LIST = ? where EMAIL_LIST_DOWNLOAD_HISTORY_ID = ?");
		MerlinBlob blob = new MerlinBlob(byteData);
		pstmt.setBlob(1,blob);
		pstmt.setInt(2,recordId);
		int updateCount = pstmt.executeUpdate();

		pstmt.close();

		return updateCount;
	}


	public int updateEmailListingDownloadDate(int recordId)throws SQLException{
		int updateCount = -1;

		PreparedStatement pstmt = con.prepareStatement("UPDATE MSAL_EMAIL_LIST_DOWNLOAD_HISTORY SET DOWNLOAD_DATE = CURRENT_DATE WHERE EMAIL_LIST_DOWNLOAD_HISTORY_ID = ?");
		pstmt.setInt(1,recordId);
		updateCount = pstmt.executeUpdate();
		pstmt.close();

		return updateCount;
	}

	public EmailListingBean[] getEmailListingBeans()throws SQLException{

		List arrRecords = new ArrayList();
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT EMAIL_LIST_DOWNLOAD_HISTORY_ID,EMAIL_LIST_DESCRIPTION,CREATION_DATE,DOWNLOAD_DATE,EMAIL_LIST from MSAL_EMAIL_LIST_DOWNLOAD_HISTORY order by EMAIL_LIST_DOWNLOAD_HISTORY_ID desc");
		while(rs.next()){
			EmailListingBean bean = new EmailListingBean();
			bean.setListingId(rs.getInt(1));
			bean.setDescription(rs.getString(2));
			bean.setCreationDate(rs.getDate(3));
			bean.setDownloadDate(rs.getDate(4));
			bean.setListingAvailable((rs.getBlob(5) != null ? true:false));

			arrRecords.add(bean);
		}

		rs.close();
		stmt.close();

  		return (EmailListingBean[]) arrRecords.toArray(new EmailListingBean[0]);
	}

	public EmailListingBean getEmailListingBeanByListingId(int listingId)throws SQLException{
  		EmailListingBean bean = null;

  		PreparedStatement pstmt = con.prepareStatement("select EMAIL_LIST,EMAIL_LIST_DESCRIPTION,CREATION_DATE from MSAL_EMAIL_LIST_DOWNLOAD_HISTORY where EMAIL_LIST_DOWNLOAD_HISTORY_ID = ?");
  		pstmt.setInt(1,listingId);
  		ResultSet rs = pstmt.executeQuery();
  		if(rs.next()){
  			bean = new EmailListingBean();
  			Blob blob = rs.getBlob(1);
  			if(blob != null){
  				int length = (int)blob.length();
  				bean.setFileData(blob.getBytes(1,length));
  				bean.setDescription(rs.getString(2));
  				bean.setCreationDate(rs.getDate(3));
  			}
  		}

  		rs.close();
  		pstmt.close();

  		return bean;
	}

	public int deleteEmailListings(String listingIds)throws SQLException{
		String sql = "delete from MSAL_EMAIL_LIST_DOWNLOAD_HISTORY where EMAIL_LIST_DOWNLOAD_HISTORY_ID in (" + listingIds + ")";
		Statement stmt = con.createStatement();
		int result = stmt.executeUpdate(sql);
		stmt.close();
		return result;
	}
}